The I in ACID
Postgres Vision
2020-06-24
The I in ACID
ACID
Transaction
Isolation
Use cases
ACID
Atomicity
Consistency
Isolation
Durability
Jim Gray (award Turing in 1998) invented atomicity,
consistency and durability concepts.
Isolation was added later in 1983.
Atomicity
An atomic transaction is an indivisible and
irreducible series of database operations such that
either all occur, or nothing occurs.
Wikipedia
Consistency
Consistency in database systems refers to the
requirement that any given database transaction must
change affected data only in allowed ways.
Wikipedia
Isolation
In database systems, isolation determines how
transaction integrity is visible to other users and
systems.
Wikipedia
Durability
In database systems, durability is the ACID property
which guarantees that transactions that have
committed will survive permanently.
Wikipedia
Read uncommitted
One transaction may see not-yet-committed changes made by
other transactions.
Wikipedia
PostgreSQL's Read Uncommitted mode behaves like
Read Committed.
Postgres documentation
Read uncommitted
If the transaction isolation level of the
SQL-transaction is READ UNCOMMITTED, then any
<level of isolation> is permissible.
SQL 2016 standard
Read committed
Read committed is an isolation level that
guarantees that any data read is committed at the moment it is
read. It simply restricts the reader from seeing
any intermediate, uncommitted, 'dirty' read.
Wikipedia
Repeatable read
The Repeatable Read isolation level only sees data
committed before the transaction began.
Postgres documentation
Serializable
This level emulates serial transaction execution
for all committed transactions; as if transactions
had been executed one after another, serially,
rather than concurrently.
Postgres documentation
Dirty read
Dirty read
Dirty read
Dirty read
Lost update
Lost update
Lost update
Lost update
Lost update
Non-repeatable read
A transaction re-reads data it has previously read
and finds that data has been modified by another
transaction (that committed since the initial read).
Wikipedia
Non-repeatable read
Non-repeatable read
Non-repeatable read
Non-repeatable read
Non-repeatable read
Non-repeatable read
Phantom read
A transaction re-executes a query returning a set of
rows that satisfy a search condition and finds that
the set of rows satisfying the condition has changed
due to another recently-committed transaction.
Postgres documentation
Phantom read
Phantom read
Phantom read
Serialization anomaly
The result of successfully committing a group of
transactions is inconsistent with all possible
orderings of running those transactions one at a
time.
Postgres documentation
Serialization anomaly
Serialization anomaly
Serialization anomaly
Serialization anomaly
Serialization anomaly
Serialization anomaly
Anomalies and isolation levels
Isolation Level
Read uncommitted
Read committed
Repeatable read
Serializable
Dirty Read
Lost update
Non-repeatable
Read
Phantom Read
Serialization
Anomaly
Anomalies and isolation levels (For Postgres)
Isolation Level
Read uncommitted
Read committed
Repeatable read
Serializable
Dirty Read
Lost update
Non-repeatable
Read
Phantom Read
Serialization
Anomaly
MVCC
Multiversion concurrency control (MCC or MVCC), is a
concurrency control method commonly used by database
management systems to provide concurrent access to
the database.
Wikipedia
Implementation: keeping old version of data
Locks
Record locking is the technique of preventing
simultaneous access to data in a database, to prevent
inconsistent results.
Wikipedia
Dead lock
A deadlock is a state in which each member of a group
is waiting for another member, including itself, to
take action, such as sending a message or more
commonly releasing a lock.
Wikipedia
Dead lock
Dead lock
Dead lock
Dead lock
Dead lock
How to use that
Is it ok to have...
Dirty reads ?
Lost updates ?
Non-repeatable reads ?
Phantom reads ?
Serialization anomalies ?
Most of the time
Is it ok to have...
Dirty reads ? Probably not
Lost updates ? Probably
Non-repeatable reads ?
Probably
Phantom reads ? Probably
Serialization anomalies ?
Probably
Most of the time
→ Read committed
Pagination
Is it ok to have...
Dirty reads ? Probably not
Lost updates ? Probably not
Non-repeatable reads ?
It depends
Phantom reads ? Probably
Serialization anomalies ?
Probably
Pagination
→ Read committed or Repeatable
read
Inventory level
Is it ok to have...
Dirty reads ? Probably not
Lost updates ? Probably not
Non-repeatable reads ?
Probably not
Phantom reads ? Probably not
Serialization anomalies ?
Probably
Inventory level
→ Repeatable read
Invoice numbers
Is it ok to have...
Dirty reads ? Probably not
Lost updates ? Probably not
Non-repeatable reads ?
Probably not
Phantom reads ? Probably not
Serialization anomalies ?
Probably not
Invoice numbers
→ Serializable